On Database and SQL
__TOC__ Database General * How to select multiple rows filled with constants? * Comparison of MPP Data Warehouse Platforms (Jan 24, 2013) * ANSI SQL Data Types Tips and Tricks The length of identifiers by database * References ** Oracle Database 11g Schema Object Names and Qualifiers *** Names must be from 1 to 30 bytes long with these exceptions: ... Transaction * Logging Last Resource Transaction Optimization * Enlisting multiple 1-phase aware participants in the same transaction * On Transaction Isolation Levels * Understanding Isolation Levels SQL * SQL Tutorial from W3Schools Articles * Meet the experts: Terry Purcell on coding predicates in outer joins: A comparison of simple outer join constructs (Part 1 of 2, 04 December 2001) Tips and Tricks * Get simplest timestamp string in 'yyyyMMddHHmmss' format Oracle Readings References * Oracle Database 11g Documentation Library ** SQL Language Reference * Oracle Database 10g Documentation Library * Oracle Database XE 11g Release 2 Documentation * System Privileges (Oracle database 10g) * SET System Variable Summary (Oracle database 10g) * Basic Initialization Parameters (Oracle database 11g) * Data Concurrency and Consistency (Oracle database 10g) Articles Administration * SYS, SYSTEM, and SYSDAB * How to authenticate an administrator using the operating system. * Logging In and Connecting to the Database as SYSDBA (Oracle XE) * tnsnames.ora * Configuring the Network Environment * Oracle environment variables * Change Oracle JDBC Thin Client Identifier * Changing Listener Port Numbers of Oracle XE 11g * SGA target Application Development * Oracle Row Generator Techniques * On ROWNUM and Limiting Results * SQL*Plus - Oracle FAQ ** a command line SQL and PL/SQL language interface and reporting tool that ships with the Oracle Database Client and Server software. * SQL*Plus FAQ * SQL*Plus, EZCONNECT, Password Prompt, and ORA-12504 * SQL*Net - Oracle FAQ ** Oracle's networking software that allows remote data-access between programs and the Oracle Database, or among multiple Oracle Databases. * Introduction to Pro*C Performance Tuning * EXPLAIN PLAN Usage * DBMS_XPLAN : Display Oracle Execution Plans * [http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::p11_question_id:385254862843 Whats the difference between the v$sql* views] * Real-Time SQL Monitoring * [http://www.dba-oracle.com/t_v_sql_monitor.htm Using v$sql_monitor] misc * Oracle: Foreign Keys in alternate schemas(Aug 30 '12) * How to grant select on multiple table at same time(Mar 19, 2007) Tips Listing all schemas or all objects in a schema In the case that you've logged in a database, but you have entirely no idea what the database is like, you may need the following query first. SELECT username FROM dba_users And then, you may need the next query on the schema you want to know more. SELECT DISTINCT owner, object_type, object_name FROM dba_objects WHERE owner = 'HR' If you don't have DBA privilege, just use all_object table. SELECT DISTINCT owner, object_type, object_name FROM all_objects WHERE owner = 'HR' Finding roles and privileges of a user or current session. With more emphasis on securities, default privileges for a regular(non-DBA) user is very restrictive. As of Oracle database 10.2, CONNECT and RESOURCE which are typical predefined roles for a regular user don't include create view and create synonym privileges. So often, you should have to check what roles and privileges are given for a user. You can do that using the following statements. -- finding all roles for the current session SELECT * FROM SESSION_ROLES; -- finding all privileges for the current session SELECT * FROM SESSION_PRIVS; Required roles to access dynamic performance (V$) views. To access the dynamic performance views such as V$SESSION, V$PARAMETER and V$LOCK as well as static dictionary views of DBA such as DBA_TABLES, DBA_SEQUENCES and DBA_SYNONYMS, the user need SELECT_CATALOG_ROLE DBA user can grant the role to the user using the following statement. GRANT select_catalog_role TO :username; Finding the values of parameters currently in effect. Checking the context of current operation is really one of the most basic thing you can do in any circumstances. With Oracle, that is parameters. You can identify the current value of SGA max size, max sessions, optimizer mode or other settings throughout the parameter. SELECT * FROM V$PARAMETER ORDER BY name; V$PARAMETER is one of public synonyms in default installation. Identifying the character-set of the Oracle instance Use the following statement. select * from nls_database_parameters where parameter = 'NLS_CHARACTERSET'; Granting minimum privileges to a user The default roles or privileges granted to a user right after the create varies with versions of Oracle database. As an increasing emphasis of security, the default roles or privileges seems to reduced at the more newer versions. For a owner of a specific schema, the minimum privileges required for the user to build schema objects and process statements are the followings CREATE SESSION, CREATE TABLE, CREATE VIEW, CREATE SEQUENCE, CREATE SYNONYM, CREATE PUBLIC SYNONYM, CREATE PROCEDURE, CREATE TRIGGER, UNLIMITED TABLESPACE To grant those privileges to a user, use the following statement grant CREATE SESSION, CREATE TABLE, CREATE VIEW, CREATE SEQUENCE, - CREATE SYNONYM, CREATE PUBLIC SYNONYM, CREATE PROCEDURE, CREATE TRIGGER, UNLIMITED TABLESPACE to :user; Changing the password of the user Use the following statement. ALTER USER :username IDENTIFIED BY :password; Be cautious not to mark single quotation mark around values of :username and :password. The following is simple example. ALTER USER hr IDENTIFIED BY hr88rh; When you are connected to Oracle using SQL*Plus, you can use password command to change your password. The following is sample command line when changing password of hr user SQL>password Changing password for hr Old password : New password : Retype new password : Password changed Viewing information about users About current user, access the following dictionary views. More privileged users can use the following views to access the information of all users. Use connect command of SQL*Plus without tnsnames.ora * How to connect SQLPlus without tnsnames.ora Identifying the sessions currently logged-on You need select_catalog_role and execute the following query SELECT sid, serial#, username, status, schemaname, machine, terminal, program, type, logon_time FROM V$Session where type <> 'BACKGROUND' order by logon_time desc; Identifying execution plan of query Using DBMS_XPLAN.DISPLAY_CURSOR function The execution plan of last executed query in plan table can be printed out using DBMS_XPLAN.DISPLAY_CURSOR function. DBMS_XPLAN package was introduced at Oracle 9i, but DISPLAY_CURSOR function was added with Oracle 10g. DBMS_XPLAN.DISPLAY_CURSOR function accesses V$SESSION, so the user need to have SELECT_CATALOG_ROLE role to correctly execute select * from table(DBMS_XPLAN.DISPLAY_CURSOR) select employee_id, first_name || ' ' || last_name as name, department_id, job_id, manager_id from hr.employees where job_id = 'SA_REP' and manager_id in (146, 147); select * from table(DBMS_XPLAN.DISPLAY_CURSOR); If you want to see just execution plan and not to actually execute the query. You can use EXPLAIN PLAN statement. explain plan for select employee_id, first_name || ' ' || last_name as name, department_id, job_id, manager_id from hr.employees where job_id = 'SA_REP' and manager_id in (146, 147); select * from table(DBMS_XPLAN.DISPLAY_CURSOR); Using autotrace with SQL*Plus When using SQL*Plus, you can turn on autotrace feature for the current session using SET AUTOTRACE ON command. After the autotrace is enabled, execution of query would display normal result data and the execution plan right after it. To use this feature, you must create a PLAN_TABLE table in your schema and then have the PLUSTRACE role granted to you. SQL>SET AUTOTRACE ON SQL>SELECT employee_id, first_name || ' ' || last_name as name, department_id, job_id, manager_id 2 FROM hr.employees 3 WHERE job_id = 'SA_REP' 4 AND manager_id IN (146, 147); If you want to see just execution plan and not to actually execute the query, you can use SET AUTOTRACE TRACEONLY command. SQL>SET AUTOTRACE TRACEONLY SQL>SELECT employee_id, first_name || ' ' || last_name as name, department_id, job_id, manager_id 2 FROM hr.employees 3 WHERE job_id = 'SA_REP' 4 AND manager_id IN (146, 147); Readings * DBMS_XPLAN of Oracle Database 10.2 * Tracing Statements of Oracle Database 10.2 * EXPLAIN PLAN statement of Oracle Database 10.2 MySQL Readings References * MySQL 5.6 Reference Manual ** Server System Variables ** Using Option Files ** User Account Management *** Specifying Account Names ** Starting MySQL as a Windows Service * MySQL 5.0 Reference Manual SQL Statement Syntax * MySQL 5.6 SQL Statement Syntax * MySQL 5.0 SQL Statement Syntax * Database Administration Statements ** [http://dev.mysql.com/doc/refman/5.6/en/show.html SHOW Syntax] ** [http://dev.mysql.com/doc/refman/5.6/en/set-statement.html SET Syntax] ** Account Management Statements * Strict SQL Mode * Constraints on Invalid Data Tutorials and Articles * MySql Select, MANY FIELDS, how best to display in terminal? (May 29 '09) Tips Using command line After log-in on unfamiliar database To identify the server address, port and current user, use status command. mysql>status To identify the databases available, use SHOW DATABASES statement. mysql>show databases; To identify the privileges granted to the current user, use SHOW GRANTS statement. mysql>SHOW GRANTS FOR CURRENT_USER; To change the password for the current user, use SET PASSWORD statement and password function. mysql>SET PASSWORD = PASSWORD('cleartext password'); To identify all the tables defined in current schema, use SHOW TABLES'' statement. If no schema is assigned yet to current session, execute '''USE statement before. mysql>USE myschema; mysql>SHOW TABLES; mysql>SHOW COLUMNS IN mytable; Adding an account mysql>create user 'sample'@'%' identified by 'password'; mysql>grant all on sample.* to 'sample'@'%'; GRANT ALL would exclude GRANT OPTION privilege. For more, refer Adding User Accounts section of MySQL manual. Installation Typical sample of my.cnf mysqld port = 3306 #socket = /tmp/mysql.sock skip-locking key_buffer = 16M max_allowed_packet = 1M table_cache = 64 sort_buffer_size = 512K net_buffer_length = 8K read_buffer_size = 256K read_rnd_buffer_size = 512K myisam_sort_buffer_size = 8M datadir=D:\home\mysql\5.0\data/ character-set-server=utf8 character-set-filesystem=utf8 collation-server=utf8_general_ci default-storage-engine=innodb innodb_data_file_path = ibdata1:30M:autoextend MySQL as a Service on Windows * Register MySQL as a Windows service C:\>"C:\servers\mysql-5.0.96\bin\mysqld" --install-manual MySQL --defaults-file=D:\home\mysql\5.0\my.cnf * Start service C:\>sc start MySQL * Stop service C:\>sc stop MySQL Microsoft SQL Server Readings References SQL Server 2012 * Transact-SQL Reference * Features ** Sequence Numbers *** Introduced from SQL Server 2012, similar with Sequence object in Oracle * Data Types ** Date and Time Data Types and Functions ** Constants * Built-in Functions ** ISNULL function ** COALESCE function * DML Statements ** Inserting Data from Other Tables * DDL Statements ** CREATE, DROP, ALTER, TRUNCATE TABLE, ENABLE TRIGGER, DISABLE TRIGGER ** IDENTITY (Property) : for generating key values. ** Create Check Constraints * System Stored Procedures ** sp_executesql Readings * Cannot insert duplicate key row even when using MAX(id) * Solving Deadlocks Through Locking Hints(Apr 01 2009) * Cannot truncate table because it is being referenced by a FOREIGN KEY constraint?(Oct 31 '08) * SQL Server Isolation Levels By Example(18 FEBRUARY 2012) SQL Tuning * How do I obtain a Query Execution Plan? (Sep 9 '11) * Execution Plan Basics (11 May 2008) * Parameters and Execution Plan Reuse Tips SELECT cp.refcounts , cp.usecounts , cp.objtype , st.dbid , st.objectid , st.text , qp.query_plan FROM sys.dm_exec_cached_plans cp CROSS APPLY sys.dm_exec_sql_text ( cp.plan_handle ) st CROSS APPLY sys.dm_exec_query_plan ( cp.plan_handle ) qp HSQLDB * Concepts ** Catalog = Information Schema, Schema* ** Schema = Object* * "In HyperSQL, there is only one catalog per database. The name of the catalog is PUBLIC." misc Sample Schemas * Oracle's sample schemas ** HR(Human Resources), OE(Order Entry), PM(Product Media), SH(Sales History) ** Schema Diagrams ** Schema Object Descriptions ** Sample Models and Scripts * Microsoft SQL Server sample databases ** AdventureWorks Sample Databases *** Schemas in AdventureWorks